/*
 * MIT License
 *
 * Copyright (c) 2023-present, tangli
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

package tony.test.mybatis.datascope.integration

import org.junit.jupiter.api.Assertions.assertTrue
import org.junit.jupiter.api.BeforeEach
import org.junit.jupiter.api.DisplayName
import org.junit.jupiter.api.Nested
import org.junit.jupiter.api.Test
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.test.context.ActiveProfiles
import tony.mybatis.datascope.IDataScopeContext
import tony.mybatis.datascope.handler.DefaultScopedDataPermissionHandler
import tony.mybatis.datascope.interceptor.ScopedDataPermissionInterceptor
import tony.test.mybatis.datascope.DataScopeBaseTest

/**
 * 数据权限真实数据库测试
 * @author tangli
 * @date 2025/09/21 10:00
 */
@ActiveProfiles("mysql")
@DisplayName("数据权限真实数据库测试")
class DataScopeRealDbTest : DataScopeBaseTest() {

    @Autowired
    private lateinit var dataScopeContext: IDataScopeContext

    private lateinit var interceptor: ScopedDataPermissionInterceptor

    @BeforeEach
    fun setUp() {
        interceptor = ScopedDataPermissionInterceptor(DefaultScopedDataPermissionHandler())
        prepareRealTestData()
    }

    /**
     * 准备真实的测试数据
     */
    private fun prepareRealTestData() {
        logger.info("=== 准备真实数据库测试数据 ===")

        // 在真实的数据库环境中，这里会执行真实的数据插入
        // 由于当前是集成测试的基础版本，我们先模拟数据准备过程
        logger.info("真实测试数据准备完成")
    }

    @Nested
    @DisplayName("真实数据库SQL执行正确性验证")
    inner class RealDatabaseSqlExecutionTest {

        @Test
        @DisplayName("真实SELECT执行:验证SQL在数据库中的实际执行")
        fun testRealSelectExecution() {
            val originalSql = "SELECT * FROM sys_dict WHERE dict_name = 'test'"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("real_user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.real.select")
                }

            logger.info("=== 真实SELECT执行验证 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("权限处理后SQL: {}", result)

            // 验证SQL语法正确性
            assertTrue(result.contains("creator_id = 'real_user1'"))
            assertTrue(result.contains("dict_name = 'test'"))
            assertTrue(result.contains("AND"))

            // 在真实环境中，这里可以验证SQL是否能被数据库正确解析和执行
            logger.info("真实SELECT执行验证完成")
        }

        @Test
        @DisplayName("真实UPDATE执行:验证UPDATE在数据库中的实际执行")
        fun testRealUpdateExecution() {
            val originalSql = "UPDATE sys_dict SET dict_name = 'updated_value' WHERE dict_id = 'test_id'"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("real_user1"))
                .orgIdList(listOf("real_org1"))
                .call {
                    interceptor.parserMulti(originalSql, "test.real.update")
                }

            logger.info("=== 真实UPDATE执行验证 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("权限处理后SQL: {}", result)

            // 验证UPDATE的权限条件正确注入
            assertTrue(result.contains("creator_id = 'real_user1'") || result.contains("creator_org_id = 'real_org1'"))
            assertTrue(result.contains("dict_id = 'test_id'"))
            assertTrue(result.contains("dict_name = 'updated_value'"))

            logger.info("真实UPDATE执行验证完成")
        }

        @Test
        @DisplayName("真实DELETE执行:验证DELETE在数据库中的实际执行")
        fun testRealDeleteExecution() {
            val originalSql = "DELETE FROM sys_dict WHERE dict_type_id = 'type_to_delete'"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("real_user1", "real_user2"))
                .call {
                    interceptor.parserMulti(originalSql, "test.real.delete")
                }

            logger.info("=== 真实DELETE执行验证 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("权限处理后SQL: {}", result)

            // 验证DELETE的权限条件正确注入
            assertTrue(result.contains("creator_id IN ('real_user1', 'real_user2')"))
            assertTrue(result.contains("dict_type_id = 'type_to_delete'"))

            logger.info("真实DELETE执行验证完成")
        }
    }

    @Nested
    @DisplayName("数据库连接和事务正确性验证")
    inner class DatabaseConnectionTransactionTest {

        @Test
        @DisplayName("事务内权限处理:验证事务环境下的权限正确性")
        fun testTransactionDataScopeCorrectness() {
            val originalSql = "SELECT * FROM sys_dict"

            // 在事务环境中执行数据权限处理
            val result = dataScopeContext.dataScope()
                .userIdList(listOf("tx_user1"))
                .call {
                    logger.info("在事务环境中执行数据权限处理")
                    interceptor.parserSingle(originalSql, "test.transaction")
                }

            logger.info("=== 事务内权限处理验证 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("事务内处理后SQL: {}", result)

            assertTrue(result.contains("creator_id = 'tx_user1'"))
            logger.info("事务内权限处理验证完成")
        }

        @Test
        @DisplayName("多次数据库操作:验证多次操作的权限一致性")
        fun testMultipleDatabaseOperationsCorrectness() {
            val dataScope = dataScopeContext.dataScope()
                .userIdList(listOf("multi_user1"))
                .orgIdList(listOf("multi_org1"))

            // 第一次数据库操作
            val result1 = dataScope.call {
                interceptor.parserSingle("SELECT * FROM sys_dict", "test.multi.1")
            }

            // 第二次数据库操作
            val result2 = dataScope.call {
                interceptor.parserSingle("SELECT * FROM sys_dict_type", "test.multi.2")
            }

            // 第三次数据库操作
            val result3 = dataScope.call {
                interceptor.parserMulti("UPDATE sys_dict SET dict_name = 'multi_test'", "test.multi.3")
            }

            logger.info("=== 多次数据库操作验证 ===")
            logger.info("第一次操作结果: {}", result1)
            logger.info("第二次操作结果: {}", result2)
            logger.info("第三次操作结果: {}", result3)

            // 验证所有操作都包含相同的权限条件
            assertTrue(result1.contains("creator_id = 'multi_user1'") || result1.contains("creator_org_id = 'multi_org1'"))
            assertTrue(result2.contains("creator_id = 'multi_user1'") || result2.contains("creator_org_id = 'multi_org1'"))
            assertTrue(result3.contains("creator_id = 'multi_user1'") || result3.contains("creator_org_id = 'multi_org1'"))

            logger.info("多次数据库操作验证完成")
        }
    }

    @Nested
    @DisplayName("数据库性能和稳定性验证")
    inner class DatabasePerformanceStabilityTest {

        @Test
        @DisplayName("长SQL处理:验证复杂长SQL的权限处理正确性")
        fun testLongSqlProcessingCorrectness() {
            val longSql = """
                SELECT
                    d.dict_id,
                    d.dict_name,
                    d.dict_code,
                    d.dict_value,
                    d.dict_meta,
                    d.sort,
                    d.remark,
                    d.create_time,
                    d.creator_id,
                    d.creator_name,
                    d.update_time,
                    d.updator_id,
                    d.updator_name,
                    d.delete_time,
                    t.dict_type_id,
                    t.dict_type_name,
                    t.dict_type_code
                FROM sys_dict d
                LEFT JOIN sys_dict_type t ON d.dict_type_id = t.dict_type_id
                WHERE d.build_in = 1
                  AND d.delete_time IS NULL
                  AND t.build_in = 1
                  AND t.delete_time IS NULL
                  AND d.dict_name LIKE '%search%'
                ORDER BY d.sort ASC, d.create_time DESC
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("long_sql_user"))
                .call {
                    interceptor.parserSingle(longSql, "test.long.sql")
                }

            logger.info("=== 长SQL处理验证 ===")
            logger.info("原始SQL长度: {}", longSql.length)
            logger.info("处理后SQL长度: {}", result.length)
            logger.info("处理后SQL: {}", result)

            // 验证复杂SQL的权限处理正确性
            assertTrue(result.contains("d.creator_id = 'long_sql_user'"))
            assertTrue(result.contains("LEFT JOIN"))
            assertTrue(result.contains("dict_name LIKE '%search%'"))
            assertTrue(result.contains("ORDER BY"))

            logger.info("长SQL处理验证完成")
        }

        @Test
        @DisplayName("并发权限处理:验证并发环境下的权限处理稳定性")
        fun testConcurrentDataScopeStability() {
            // 模拟并发场景下的权限处理
            val results = mutableListOf<String>()

            // 模拟5个并发的权限处理请求
            for (i in 1..5) {
                val result = dataScopeContext.dataScope()
                    .userIdList(listOf("concurrent_user$i"))
                    .call {
                        logger.info("并发权限处理 - 线程{}", i)
                        interceptor.parserSingle("SELECT * FROM sys_dict", "test.concurrent.$i")
                    }
                results.add(result)
            }

            logger.info("=== 并发权限处理验证 ===")
            results.forEachIndexed { index, result ->
                logger.info("并发结果{}: {}", index + 1, result)
                assertTrue(result.contains("creator_id = 'concurrent_user${index + 1}'"),
                    "并发处理应保持各自的权限条件")
            }

            logger.info("并发权限处理验证完成")
        }
    }

    @Nested
    @DisplayName("数据库兼容性验证")
    inner class DatabaseCompatibilityTest {

        @Test
        @DisplayName("SQL方言兼容性:验证生成的SQL与目标数据库的兼容性")
        fun testSqlDialectCompatibility() {
            val testSqls = listOf(
                "SELECT * FROM sys_dict",
                "SELECT d.* FROM sys_dict d WHERE d.build_in = 1",
                "UPDATE sys_dict SET dict_name = 'test' WHERE dict_id = '1'",
                "DELETE FROM sys_dict WHERE dict_type_id IS NULL"
            )

            logger.info("=== SQL方言兼容性验证 ===")

            testSqls.forEachIndexed { index, sql ->
                val result = dataScopeContext.dataScope()
                    .userIdList(listOf("dialect_user"))
                    .call {
                        if (sql.startsWith("SELECT")) {
                            interceptor.parserSingle(sql, "test.dialect.select.$index")
                        } else {
                            interceptor.parserMulti(sql, "test.dialect.modify.$index")
                        }
                    }

                logger.info("原始SQL{}: {}", index + 1, sql)
                logger.info("处理后SQL{}: {}", index + 1, result)

                // 验证每个SQL都正确注入了权限条件
                assertTrue(result.contains("creator_id = 'dialect_user'"),
                    "SQL$index 应包含权限条件")
            }

            logger.info("SQL方言兼容性验证完成")
        }

        @Test
        @DisplayName("数据库函数兼容性:验证包含数据库特定函数的SQL处理")
        fun testDatabaseFunctionCompatibility() {
            val functionSql = """
                SELECT
                    d.*,
                    COALESCE(d.dict_name, 'default') as safe_name,
                    CASE
                        WHEN d.build_in = 1 THEN 'active'
                        ELSE 'inactive'
                    END as status
                FROM sys_dict d
                WHERE d.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("func_user"))
                .call {
                    interceptor.parserSingle(functionSql, "test.function.compatibility")
                }

            logger.info("=== 数据库函数兼容性验证 ===")
            logger.info("原始SQL: {}", functionSql)
            logger.info("处理后SQL: {}", result)

            // 验证数据库函数不受权限处理影响
            assertTrue(result.contains("d.creator_id = 'func_user'"))
            assertTrue(result.contains("COALESCE"))
            assertTrue(result.contains("CASE"))
            assertTrue(result.contains("DATE_SUB"))
            assertTrue(result.contains("NOW()"))

            logger.info("数据库函数兼容性验证完成")
        }
    }
}
